package com.gsk.europepharma.salesReport.struts.actions;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Properties;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;


import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;

import com.gsk.europepharma.salesReport.hibernate.tbSpBoSRSalesUgp;
import com.gsk.europepharma.salesReport.hibernate.dao.tbSpBoSRSectoDAO;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Image;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

/**
 * Export PDF du top UGPN.
 */
public class ExportGridPdf extends Action {
	
	private static final int DEFAULT_BUFFER_SIZE = 10240; 
	
	public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
		
		// Variables de session
		HttpSession sessionObject = request.getSession();
		Integer cs_adm_pdt = (Integer) sessionObject.getAttribute("cs_adm_pdt");
		Integer cs_adm_rgpt = (Integer) sessionObject.getAttribute("cs_adm_rgpt");
		Integer cs_adm_prstn = (Integer) sessionObject.getAttribute("cs_adm_prstn");
		Integer cs_network = (Integer) sessionObject.getAttribute("cs_network");
		// Integer cs_month = (Integer) sessionObject.getAttribute("cs_month");
		// Fin de session
				
		String geog = request.getParameter("geog");
		String nivGeog = request.getParameter("nivGeog");
		
		String source = request.getParameter("source");
		Integer CS_SRC = Integer.parseInt(source);
		String unite = request.getParameter("unite");
		
		String idGeog = request.getParameter("idGeog");
		Integer idGeogInteger = Integer.parseInt(idGeog);
		
		Properties prop = new Properties();
		prop.load(Parametrage.class.getClassLoader().getResourceAsStream("config.properties"));
		
		File temp = new File(prop.getProperty("dir") + "topUGPN.pdf");
		Document document = new Document();
		PdfWriter p = PdfWriter.getInstance(document, new FileOutputStream(temp));
        document.open();
        
        // Style
        BaseColor colorHeader = new BaseColor(122, 156, 209);
        
        BaseColor colorLinePeer = new BaseColor(192, 192, 192);
        
        Font fontHeader = new Font();
        fontHeader.setColor(BaseColor.WHITE);
        fontHeader.setSize(9);
        fontHeader.setStyle(Font.BOLD);
        
        Font fontLine = new Font();
        fontLine.setSize(8);
        
        PdfPCell cell;
        
        PdfPTable table = new PdfPTable(1);
        table.setWidths(new int[] {1});
        
        cell = new PdfPCell(new Phrase("TOP UGPN sur " + geog, fontHeader));
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        document.add(table);
        
        table = new PdfPTable(5);
        table.setWidths(new int[] {3, 1, 1, 1, 1});
        
        cell = new PdfPCell(new Phrase("UGPN", fontHeader));
        cell.setRowspan(2);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        cell = new PdfPCell(new Phrase("CM3", fontHeader));
        cell.setColspan(2);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        cell = new PdfPCell(new Phrase("Mois", fontHeader));
        cell.setColspan(2);
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        cell = new PdfPCell(new Phrase("PM", fontHeader));
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        String deltaPm = "" + (char) 916 + " PM";
        
        cell = new PdfPCell(Phrase.getInstance(0, deltaPm, fontHeader));
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        cell = new PdfPCell(new Phrase("PM", fontHeader));
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        cell = new PdfPCell(Phrase.getInstance(0, deltaPm, fontHeader));
        cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        cell.setBackgroundColor(colorHeader);
        table.addCell(cell);
        
        document.add(table);
        
 		// Requete SQL
		tbSpBoSRSectoDAO salesDAO = new tbSpBoSRSectoDAO();
		Session session = salesDAO.getSession();
		Query req = null;
		String strReq = "";
		strReq = salesDAO.QUERY_TOP_UGP_ALL;
		req = session.getNamedQuery(strReq);
		req.setParameter("data1",		unite + "CC");
		req.setParameter("data2",		unite + "MM");
		req.setInteger("cs_adm_pdt",		cs_adm_pdt);
		req.setInteger("cs_adm_rgpt",	cs_adm_rgpt);
		req.setInteger("cs_adm_prstn",	cs_adm_prstn);
		req.setInteger("CS_SRC",		CS_SRC);
		req.setInteger("cs_network",	cs_network);
		req.setString("nivGeog",		nivGeog);
		req.setInteger("idGeog",		idGeogInteger); 
		ArrayList result = new ArrayList(req.list());
		Iterator iter = result.iterator();
 		
 		table = new PdfPTable(5);
        table.setWidths(new int[] {3, 1, 1, 1, 1});
 		
        BaseColor colorLine = colorLinePeer;
        
        int peer = 0;
        
        String tmpVal = "";
        
        while (iter.hasNext()) {
        	Object[] val = (Object[]) iter.next();
			
        	if ((peer % 2) == 0) {
        		colorLine = colorLinePeer;
        	} else {
        		colorLine = BaseColor.WHITE;
        	}
        	peer++;

        	cell = new PdfPCell(new Phrase(val[1].toString(), fontLine));
        	cell.setHorizontalAlignment(Element.ALIGN_LEFT);
        	cell.setBackgroundColor(colorLine);
        	table.addCell(cell);

        	if (val[2] != null) {
            	tmpVal = val[2].toString() + "%";
            } else {
            	tmpVal = "";
            }
        	cell = new PdfPCell(new Phrase(tmpVal, fontLine));
        	cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        	cell.setBackgroundColor(colorLine);
        	cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        	table.addCell(cell);

        	if (val[3] != null) {
        		tmpVal = val[3].toString();
        	} else {
        		tmpVal = "";
        	}
        	PdfPTable tableTmp = new PdfPTable(2);
        	tableTmp.setWidths(new int[] {1, 1});
            PdfPCell cellTmp;
            cellTmp = new PdfPCell(new Phrase(tmpVal, fontLine));
            cellTmp.setHorizontalAlignment(Element.ALIGN_CENTER);
            cellTmp.setBackgroundColor(colorLine);
            cellTmp.setVerticalAlignment(Element.ALIGN_MIDDLE);
            cellTmp.setBorderWidth(0f);
            tableTmp.addCell(cellTmp);
            if (val[3] != null) {
            	cellTmp = new PdfPCell(convertDeltaPmInIcon(Double.parseDouble(tmpVal), request));
            } else {
            	cellTmp = new PdfPCell(new Phrase(""));
            }
            cellTmp.setHorizontalAlignment(Element.ALIGN_CENTER);
            cellTmp.setBackgroundColor(colorLine);
            cellTmp.setVerticalAlignment(Element.ALIGN_MIDDLE);
            cellTmp.setBorderWidth(0f);
            tableTmp.addCell(cellTmp);
            cell = new PdfPCell(tableTmp);
            cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        	cell.setBackgroundColor(colorLine);
        	cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
            table.addCell(cell);
            
            if (val[4] != null) {
            	tmpVal = val[4].toString() + "%";
            } else {
            	tmpVal = "";
            }
        	cell = new PdfPCell(new Phrase(tmpVal, fontLine));
        	cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        	cell.setBackgroundColor(colorLine);
        	cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
        	table.addCell(cell);

        	if (val[5] != null) {
        		tmpVal = val[5].toString();
        	} else {
        		tmpVal = "";
        	}
        	tableTmp = new PdfPTable(2);
        	tableTmp.setWidths(new int[] {1, 1});
            cellTmp = new PdfPCell(new Phrase((tmpVal), fontLine));
            cellTmp.setHorizontalAlignment(Element.ALIGN_CENTER);
            cellTmp.setBackgroundColor(colorLine);
            cellTmp.setVerticalAlignment(Element.ALIGN_MIDDLE);
            cellTmp.setBorderWidth(0f);
            tableTmp.addCell(cellTmp);
            if (val[5] != null) {
            	cellTmp = new PdfPCell(convertDeltaPmInIcon(Double.parseDouble(tmpVal), request));
            } else {
            	cellTmp = new PdfPCell(new Phrase(""));
            }
            cellTmp.setHorizontalAlignment(Element.ALIGN_CENTER);
            cellTmp.setBackgroundColor(colorLine);
            cellTmp.setVerticalAlignment(Element.ALIGN_MIDDLE);
            cellTmp.setBorderWidth(0f);
            tableTmp.addCell(cellTmp);
            cell = new PdfPCell(tableTmp);
            cell.setHorizontalAlignment(Element.ALIGN_CENTER);
        	cell.setBackgroundColor(colorLine);
        	cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
            table.addCell(cell);
        }
        
        document.add(table);
        
        document.close();
        
		BufferedInputStream input = null;
        BufferedOutputStream output = null;
        // Ouverture du fichier 
        input = new BufferedInputStream(new FileInputStream(temp), DEFAULT_BUFFER_SIZE);
        // Initialisation de la reponse
        response.reset();
        response.setHeader("Content-Type", "application/pdf");
        response.setHeader("Content-Length", String.valueOf(temp.length()));
        response.setHeader("Content-Disposition", "attachment; filename=\"" + "topUGPN.pdf" + "\"");
        output = new BufferedOutputStream(response.getOutputStream(), DEFAULT_BUFFER_SIZE);
        // Ecriture dans la réponse
        byte[] buffer = new byte[DEFAULT_BUFFER_SIZE];
        int length;
        while ((length = input.read(buffer)) > 0) {
            output.write(buffer, 0, length);
        }
        output.flush();
        output.close();
        input.close();
        response.getOutputStream().flush();

		return null;
	}
	
	private Image convertDeltaPmInIcon(Double deltaDouble, HttpServletRequest request) throws Exception {
	    String path = "";
		if (deltaDouble >= 0) {
			path = request.getRealPath("/img/cercle-vert.png");
		} else if (deltaDouble < -0.5) {
			path = request.getRealPath("/img/cercle-rouge.png");
		} else {
			path = request.getRealPath("/img/cercle-jaune.png");
		}
		Image img = Image.getInstance(path);
		
		return img;
	}

}
